#!/usr/bin/python
#-*- coding: UTF-8 -*-

import os,sys
from time import strptime, strftime
import re
import fileinput
import MySQLdb

db = MySQLdb.connect(host="localhost",user="root",passwd="ghost1221",db="icq",charset="utf8")
c = db.cursor()
channelSql = "SELECT cid,channel_name,owner_department_id FROM `traffic_statistics_channel`;"
groupSql = "SELECT cyid,group_name,owner_department_id FROM `traffic_statistics_group`;"
c.execute(channelSql)
channel = c.fetchall()
c.execute(groupSql)
group = c.fetchall()

def GetDepartmentForChannel(sid):
    for cid,name,did in channel:
        if cid == sid:
           return did
        continue

def GetDepartmentForGroup(sid):
    for cyid,name,did in group:
        if cyid == sid:
            return did
        continue

def insertlog(sql):
    c.execute(sql)

def ImportLog():
    log_path = r"D:/tj/logs/"
    patternLog = re.compile(r'(\d+\.\d+\.\d+\.\d+)(\s-\s-\s\[)(\w+.+)(\s\+0800\])(\s\"\w+\s\/)(.+?)(\s.+\")(.+)(\"\s\".+)')
    patternCid = re.compile(r'(.+)cid=([^&]+)')
    patternCyid = re.compile(r'(.+)&cyid=([^&]+)')
    patternAid = re.compile(r'(.+)&aid=([^&]+)')

    for file in os.listdir(log_path):
        if not file.endswith(".log"):
            print "WARN:%s is not a log file" %(file)
            continue
        print "INFO:process file %s" %(file)

        for line in fileinput.input(os.path.join(log_path, file)):
            m = patternLog.match(line)
            bdid=''
            ip = m.group(1)
            olddate = m.group(3)
            dt = ConvertMySQLDate(olddate)
            mid=m.group(6)
            bcid = patternCid.match(mid)
            bcyid = patternCyid.match(mid)
            baid = patternAid.match(mid)
            if bcid:
                cid=bcid.group(2)
                sid=int(cid,10)
                bdid=GetDepartmentForChannel(sid)
                if not bdid:
                    bdid=0
            else:
                cid='-9999'
            if bcyid:
                cyid = bcyid.group(2)
                sid=int(cyid,10)
                bdid=GetDepartmentForGroup(sid)
                if not bdid:
                    bdid=0
            else:
                cyid='-9999'
            if baid:
                aid = baid.group(2)
            else:
                aid='null'
            url = m.group(8)
            if bdid:
                insertsql='insert into traffic_statistics_log (department_id_id,cid_id,cyid_id,aid,ip,dt,url) values ('+str(bdid)+','+cid+','+cyid+','+aid+',"'+ip+'","'+dt+'","'+url+'");'
                c.execute(insertsql)
            else:
                insertsql='insert into traffic_statistics_undefinedlog (cid,cyid,aid,ip,dt,url) values ('+cid+','+cyid+','+aid+',"'+ip+'","'+dt+'","'+url+'");'
                c.execute(insertsql)
                #print insertsql
            #print bdid,ip,dt,cid,cyid,aid,url
            #print insertsql
            #

#转换MySQL时间格式
def ConvertMySQLDate(olddate):
    newdate = strptime(olddate, "%d/%b/%Y:%H:%M:%S")
    mysqldate = strftime("%Y-%m-%d %H:%M:%S", newdate)
    return mysqldate

ImportLog()

#提交到数据库
db.commit()
#关闭数据库连接
c.close()
db.close()